
!pip install -q -r https://raw.githubusercontent.com/snowflakedb/snowflake-connector-python/v2.7.3/tested_requirements/requirements_39.reqs

!export TMPDIR=/large_tmp_volume
# %env TMPDIR=/drive/My Drive/Personal/Snowflake


import pandas as pd
import json
import geopandas as gpd
from shapely import wkt
from shapely.geometry import Polygon, shape
from folium import Map, Marker, GeoJson
import branca.colormap as cm
import geojson
from geojson.feature import *
from geojson import Feature, FeatureCollection


import snowflake.connector


f = open('credentials.json')
data = json.load(f)
f.close()

conn = snowflake.connector.connect(
    user=data["user"],
    password=data["password"],
    account=data["account"]
    )


cur = conn.cursor()
cur.execute("create or replace database geo_playground;")
cur.execute("use schema geo_playground.public")
cur.execute("create or replace view spain_h3 as \
            (select g.GEOID, g.GEOM, d.POPULATION \
             from SPAIN_H3.CARTO.DERIVED_SPATIALFEATURES_ESP_H3RES8_V1_YEARLY_V2 d \
             join SPAIN_H3.CARTO.GEOGRAPHY_ESP_H3RES8_V1 g on g.GEOID = d.GEOID);")
cur.execute("select * from spain_h3 where POPULATION > 500")
df = cur.fetch_pandas_all()


df.head()


df['GEOM'] = df['GEOM'].apply(lambda x: geojson.loads(x))


def dataframe_to_geojson(df_geo, column_name, file_output = None):
    
    '''Produce the GeoJSON for a dataframe that has a geometry column in geojson format already'''
    
    list_features = []
    
    for i,row in df_geo.iterrows():
        feature = Feature(geometry = row["GEOM"], properties = {column_name : row[column_name]})
        list_features.append(feature)
        
    feat_collection = FeatureCollection(list_features)
    
    geojson_result = json.dumps(feat_collection)
    
    #optionally write to file
    if file_output is not None:
        with open(file_output,"w") as f:
            json.dump(feat_collection,f)
    
    return geojson_result


def choropleth_map(df_aggreg, column_name, border_color = 'none', fill_opacity = 0.5, initial_map = None, with_legend = True,
                   kind = "linear"):
    #colormap
    min_value = df_aggreg[column_name].min()
    max_value = df_aggreg[column_name].max()
    m = round ((min_value + max_value ) / 2 , 0)
    
    
    if initial_map is None:
        initial_map = Map(location= [41.390333,2.1647773], zoom_start=11, tiles="cartodbpositron")

    #the colormap 
    #color names accepted https://github.com/python-visualization/branca/blob/master/branca/_cnames.json
    if kind == "linear":
        custom_cm = cm.LinearColormap(['gray','blue','green','yellow','orange','red'], vmin=min_value, vmax=max_value)
    elif kind == "outlier":
        #for outliers, values would be -11,0,1
        custom_cm = cm.LinearColormap(['white','gray','black'], vmin=min_value, vmax=max_value)
    elif kind == "filled_nulls":
        custom_cm = cm.LinearColormap(['sienna','green','yellow','red'], 
                                      index=[0,min_value,m,max_value],vmin=min_value,vmax=max_value)
   
    #create geojson data from dataframe
    geojson_data = dataframe_to_geojson(df_geo = df_aggreg, column_name = column_name)
    
    #plot on map
    name_layer = "Choropleth "
    if kind != "linear":
        name_layer = name_layer + kind
        
    GeoJson(
        geojson_data,
        style_function=lambda feature: {
            'fillColor': custom_cm(feature['properties'][column_name]),
            'color': border_color,
            'weight': 1,
            'fillOpacity': fill_opacity 
        }, 
        name = name_layer
    ).add_to(initial_map)

    #add legend (not recommended if multiple layers)
    if with_legend == True:
        custom_cm.add_to(initial_map)
        
    return initial_map


map = choropleth_map(df_aggreg = df, column_name = "POPULATION")
map


len(df)

16488

	GEOID	GEOM	POPULATION
0	8839446065fffff	{\n "coordinates": [\n [\n [\n ...	2563.029698
1	88392d1ab1fffff	{\n "coordinates": [\n [\n [\n ...	681.371578
2	88184b8d91fffff	{\n "coordinates": [\n [\n [\n ...	776.731446
3	8839446321fffff	{\n "coordinates": [\n [\n [\n ...	4810.222257
4	883925cc2dfffff	{\n "coordinates": [\n [\n [\n ...	954.065804